# -*- coding: utf-8 -*-
# --------------------------------------
# @Time    : 2025/8/16 12:14
# @File    : tablemodel.py
# @Project : app
# @Author  : CHH
# @Desc    : Windows   文件描述
# @Copyright : © 2025 HORDOR . All rights reserved.
# @Version : 1.0.0
# 生成：sqlacodegen mysql+pymysql://root:My%4020241103@localhost/woniunote --tables users,article > model.py
# --------------------------------------
import os.path
import random, os
import time

from flask import session, jsonify
from sqlalchemy import DateTime, Integer, String, text, Index, VARCHAR, func, Date, Text
from sqlalchemy.dialects.mysql import MEDIUMTEXT, TINYINT, LONGTEXT
from sqlalchemy.orm import DeclarativeBase, Mapped, mapped_column
import datetime
from typing import Optional
from ..extensions import db
from utils.model_to_list import model_join_list
from pathlib import Path
# 导入头像数量
from config.settings import AVATAR_COUNT


class Users(db.Model):
    __tablename__ = "users"

    userid: Mapped[int] = mapped_column(Integer, primary_key=True)
    username: Mapped[str] = mapped_column(String(50))
    password: Mapped[str] = mapped_column(String(32))
    role: Mapped[str] = mapped_column(String(10), server_default=text("'user'"))
    credit: Mapped[int] = mapped_column(Integer, server_default=text("'50'"))
    nickname: Mapped[Optional[str]] = mapped_column(String(30))
    avatar: Mapped[Optional[str]] = mapped_column(String(20))
    qq: Mapped[Optional[str]] = mapped_column(String(20))
    createtime: Mapped[Optional[datetime.datetime]] = mapped_column(DateTime)
    updatetime: Mapped[Optional[datetime.datetime]] = mapped_column(DateTime)
    last_login_date: Mapped[Optional[datetime.date]] = mapped_column(Date)

    def find_all_user(self):
        res = db.session.query(Users).all()
        return res

    # 查询用户名是否已经注册，也可用于登录校验
    def find_by_username(self, username):
        result = db.session.query(Users).filter_by(username=username).all()
        return result

    # 实现注册，首次注册时用户只需要输入用户名和密码及邮箱（qq字段），所以只需要三个参数。
    # 注册时，在模型类中为其他字段尽力生成一些可用的值，虽不全面，但可用。
    # 通常用户注册时不建议填写太多资料，影响体验，可待用户后续逐步完善。
    def do_register(self, username, password, eamil):
        ctime = datetime.datetime.now()
        nickname = eamil.split('@')[0]  # 默认将邮箱账号前缀作为昵称
        avatar = str(random.randint(1, AVATAR_COUNT))  # 从15张头像图片(名字1-15)中随机选择一张
        user = Users(username=username, password=password, role='user', credit=50,
                     nickname=nickname, avatar=avatar + '.png', createtime=ctime, updatetime=ctime, qq=eamil)
        db.session.add(user)
        db.session.commit()
        return user

    # 修改用户积分
    def update_credit(self, credit):
        user_obj = db.session.query(Users).filter_by(userid=session.get('userid')).one()
        user_obj.credit = int(user_obj.credit) + credit
        db.session.commit()

    # 修改密码
    def update_pwd(self, username, newpwd, email):
        user = db.session.query(Users).filter(Users.username == username, Users.qq == email).all()
        # print(user)
        if len(user) > 0:
            user[0].password = newpwd
            db.session.commit()
            return "reset-pass"
        else:
            return "reset-error"

    # 更新上一次的登录日期,如果不等于今天，则用户积分加1.
    def update_login_info(self):
        now = datetime.datetime.utcnow()
        today = now.date()
        # 检查是否是今天第一次登录
        row = db.session.query(Users).filter_by(userid=session.get('userid')).first()
        if row.last_login_date != today:
            row.credit += 1
            row.last_login_date = today  # 更新登录日期
            db.session.commit()


class Article(db.Model):
    __tablename__ = "article"

    articleid: Mapped[int] = mapped_column(Integer, primary_key=True)
    headline: Mapped[str] = mapped_column(String(255))
    content: Mapped[str] = mapped_column(MEDIUMTEXT)
    type: Mapped[int] = mapped_column(TINYINT)
    accessible: Mapped[int] = mapped_column(TINYINT, server_default=text("'1'"))
    userid: Mapped[Optional[int]] = mapped_column(Integer)
    category: Mapped[Optional[int]] = mapped_column(TINYINT)
    thumbnail: Mapped[Optional[str]] = mapped_column(String(100))
    credit: Mapped[Optional[int]] = mapped_column(Integer, server_default=text("'0'"))
    readcount: Mapped[Optional[int]] = mapped_column(Integer, server_default=text("'0'"))
    replycount: Mapped[Optional[int]] = mapped_column(Integer, server_default=text("'0'"))
    recommended: Mapped[Optional[int]] = mapped_column(TINYINT, server_default=text("'0'"))
    hide: Mapped[Optional[int]] = mapped_column(TINYINT, server_default=text("'0'"))
    drafted: Mapped[Optional[int]] = mapped_column(TINYINT, server_default=text("'0'"))
    checked: Mapped[Optional[int]] = mapped_column(TINYINT, server_default=text("'1'"))
    createtime: Mapped[Optional[datetime.datetime]] = mapped_column(DateTime)
    updatetime: Mapped[Optional[datetime.datetime]] = mapped_column(DateTime)

    # 查询所有文章
    def find_all(self):
        result = db.session.query(Article).all()
        return result

    # 根据文章id查询文章,同时获得作者昵称，这个点击打开文章会用到.返回 (Article,'nickname')
    def find_by_id(self, articleid):
        row = db.session.query(Article, Users.nickname).join(Users, Users.userid == Article.userid).filter(
            Article.hide == 0, Article.drafted == 0, Article.checked == 1, Article.accessible == 1,
            Article.articleid == articleid).first()
        return row

    # 根据文章id更新文章的阅读次数
    def update_read_count(self, articleid):
        article_obj = db.session.query(Article).filter_by(articleid=articleid).first()
        article_obj.readcount += 1
        db.session.commit()

    # 根据指定分页的limit和offset参数值，同时与用户表做连接查询，首页会用到
    def find_limit_with_users(self, start, count):
        result = db.session.query(Article, Users).join(Users, Users.userid == Article.userid).filter(
            Article.hide == 0, Article.drafted == 0, Article.checked == 1, Article.accessible == 1).order_by(
            Article.articleid.desc()).limit(count).offset(start).all()
        return result

    # 获得文章总数量
    def get_total_count(self):
        count = db.session.query(Article).filter(
            Article.hide == 0, Article.drafted == 0, Article.checked == 1, Article.accessible == 1).count()
        return count

    # 根据文章类型获取文章
    def find_by_type(self, type, start, count):
        result = db.session.query(Article, Users).join(Users, Users.userid == Article.userid).filter(
            Article.hide == 0, Article.drafted == 0, Article.checked == 1, Article.accessible == 1,
            Article.type == type).order_by(
            Article.articleid.desc()).limit(count).offset(start).all()
        return result

    # 获得某类型文章总数量
    def get_count_by_type(self, type):
        count = db.session.query(Article).filter(
            Article.hide == 0, Article.drafted == 0, Article.checked == 1, Article.accessible == 1,
            Article.type == type).count()
        return count

    # 通过搜索框中的关键字搜索文章标题含有关键字的文章
    def find_by_searchbox(self, headline, start, count):
        result = db.session.query(Article, Users).join(Users, Users.userid == Article.userid).filter(
            Article.hide == 0, Article.drafted == 0, Article.checked == 1, Article.accessible == 1,
            Article.headline.like('%' + headline + '%')).order_by(
            Article.articleid.desc()).limit(count).offset(start).all()
        return result

    # 获得搜索框关键字相关文章的总量
    def get_count_by_searchbox(self, headline):
        count = db.session.query(Article).filter(
            Article.hide == 0, Article.drafted == 0, Article.checked == 1, Article.accessible == 1,
            Article.headline.like('%' + headline + '%')).count()
        return count

    # 最多阅读文章
    def find_most_5(self):
        result = db.session.query(Article.articleid, Article.headline).filter(Article.hide == 0, Article.drafted == 0,
                                                                              Article.checked == 1,
                                                                              Article.accessible == 1).order_by(
            Article.readcount.desc()).limit(5).all()
        return result

    # 推荐，如果超过库里超过5篇，随机选5篇
    def find_recommend_5(self):
        result = db.session.query(Article.articleid, Article.headline).filter(Article.hide == 0, Article.drafted == 0,
                                                                              Article.checked == 1,
                                                                              Article.accessible == 1,
                                                                              Article.recommended == 1).order_by(
            func.rand()).limit(5).all()
        return result

    # 返回最多阅读和推荐文章的标题
    def find_most_recommend(self):
        most = self.find_most_5()
        recommend = self.find_recommend_5()
        return most, recommend

    # 根据文章编号查询文章标题
    def find_headline_by_id(self, articleid):
        row = db.session.query(Article.headline).filter_by(articleid=articleid).first()
        return row.headline

    # 获取当前文章的上一篇和下一篇
    def find_prev_next_by_id(self, articleid):
        dict = {}
        # 查询比当前编号小的当中最大的一个
        row = db.session.query(Article).filter(Article.hide == 0, Article.drafted == 0, Article.checked == 1,
                                               Article.accessible == 1,
                                               Article.articleid < articleid).order_by(Article.articleid.desc()).limit(
            1).first()
        # 如果当前已经是第一篇，上一篇也是当前文章
        if row is None:
            prev_id = articleid
        else:
            prev_id = row.articleid
        dict['prev_id'] = prev_id
        dict['prev_headline'] = self.find_headline_by_id(prev_id)
        # 查询比当前编号大的当中最小的一个
        row = db.session.query(Article).filter(Article.hide == 0, Article.drafted == 0, Article.checked == 1,
                                               Article.accessible == 1,
                                               Article.articleid > articleid).order_by(Article.articleid).limit(
            1).first()
        # 如果当前已经是最后一篇，下一篇也是当前文章
        if row is None:
            next_id = articleid
        else:
            next_id = row.articleid
        dict['next_id'] = next_id
        dict['next_headline'] = self.find_headline_by_id(next_id)
        return dict

    # 当发表或者回复评论后，为文章表字段replycount加1
    def update_replycount(self, articleid):
        row = db.session.query(Article).filter_by(articleid=articleid).first()
        row.replycount += 1
        db.session.commit()

    # 插入新建文章
    def insert_article(self, headline, content, thumbnail, type, accessible, credit=0, drafted=0, checked=1):
        now = time.strftime('%Y-%m-%d %H:%M:%S')
        userid = session.get('userid')
        # 其他字段在数据库中均已设置好默认值，无须手工插入
        article = Article(userid=userid, type=type, headline=headline, content=content,
                          thumbnail=thumbnail, credit=credit, drafted=drafted,
                          checked=checked, createtime=now, updatetime=now, accessible=accessible)
        db.session.add(article)
        db.session.commit()

        return article.articleid  # 将新的文章编号返回，便于前端页面跳转

    # 更新修改文章.根据文章编号更新文章的内容，可用于文章编辑或草稿修改，以及基于草稿的发布
    def update_article(self, articleid, headline, content, thumbnail, type, accessible, credit=0, drafted=0, checked=1):
        row = db.session.query(Article).filter_by(articleid=articleid).first()
        ctime = time.strftime('%Y-%m-%d %H:%M:%S')
        row.type = type
        row.headline = headline
        row.content = content
        row.thumbnail = thumbnail
        row.credit = credit
        row.drafted = drafted

        row.checked = checked
        row.updatetime = ctime  # 修改文章的更新时间
        db.session.commit()
        return articleid  # 继续将文章ID返回调用处


class Credit(db.Model):
    __tablename__ = 'credit'

    creditid: Mapped[int] = mapped_column(Integer, primary_key=True)
    userid: Mapped[int] = mapped_column(Integer)
    category: Mapped[Optional[str]] = mapped_column(String(10), server_default=text("''"))
    target: Mapped[Optional[int]] = mapped_column(Integer, server_default=text("'0'"))
    credit: Mapped[Optional[int]] = mapped_column(Integer)
    createtime: Mapped[Optional[datetime.datetime]] = mapped_column(DateTime)
    updatetime: Mapped[Optional[datetime.datetime]] = mapped_column(DateTime)

    # 插入积分明细数据
    def insert_detail(self, type, target, credit):
        now = datetime.datetime.now()
        credit = Credit(userid=session.get('userid'), category=type, target=target,
                        credit=credit, createtime=now, updatetime=now)
        db.session.add(credit)
        db.session.commit()

    # 检查是否已经为文章消耗过积分
    def check_paid_article(self, articleid):
        result = db.session.query(Credit).filter_by(userid=session.get('userid'), target=articleid).all()
        if len(result) > 0:
            return True
        else:
            return False


class Favorite(db.Model):
    __tablename__ = 'favorite'

    favoriteid: Mapped[int] = mapped_column(Integer, primary_key=True)
    articleid: Mapped[Optional[int]] = mapped_column(Integer)
    userid: Mapped[Optional[int]] = mapped_column(Integer)
    canceled: Mapped[Optional[int]] = mapped_column(TINYINT, server_default=text("'0'"))
    createtime: Mapped[Optional[datetime.datetime]] = mapped_column(DateTime)
    updatetime: Mapped[Optional[datetime.datetime]] = mapped_column(DateTime)

    # 插入文章收藏数据
    def insert_favorite(self, articleid):
        row = db.session.query(Favorite).filter_by(articleid=articleid, userid=session.get('userid')).first()
        if row is not None:
            row.canceled = 0
        else:
            now = datetime.datetime.now()
            favorite = Favorite(articleid=articleid, userid=session.get('userid'), canceled=0, createtime=now,
                                updatetime=now)
            db.session.add(favorite)
        db.session.commit()

    # 取消收藏
    def cancel_favorite(self, articleid):
        row = db.session.query(Favorite).filter_by(articleid=articleid, userid=session.get('userid')).first()
        row.canceled = 1
        db.session.commit()

    # 判断是否已经被收藏
    def check_favorite(self, articleid):
        row = db.session.query(Favorite).filter_by(articleid=articleid, userid=session.get('userid')).first()
        if row is None:
            return False
        elif row.canceled == 1:
            # print(row.canceled)
            return False
        else:
            return True


class Comment(db.Model):
    __tablename__ = 'comment'

    commentid: Mapped[int] = mapped_column(Integer, primary_key=True)
    userid: Mapped[Optional[int]] = mapped_column(Integer)
    articleid: Mapped[Optional[int]] = mapped_column(Integer)
    content: Mapped[Optional[str]] = mapped_column(Text)
    ipaddr: Mapped[Optional[str]] = mapped_column(String(20))
    replyid: Mapped[Optional[int]] = mapped_column(Integer, server_default=text("'0'"))
    agreecount: Mapped[Optional[int]] = mapped_column(Integer, server_default=text("'0'"))
    opposecount: Mapped[Optional[int]] = mapped_column(Integer, server_default=text("'0'"))
    hide: Mapped[Optional[int]] = mapped_column(TINYINT, server_default=text("'0'"))
    createtime: Mapped[Optional[datetime.datetime]] = mapped_column(DateTime)
    updatetime: Mapped[Optional[datetime.datetime]] = mapped_column(DateTime)

    # 新增一条评论
    def insert_comment(self, articleid, content, ipaddr):
        ctime = datetime.datetime.now()
        comment = Comment(userid=session.get('userid'), articleid=articleid, content=content, ipaddr=ipaddr,
                          createtime=ctime, updatetime=ctime)
        db.session.add(comment)
        db.session.commit()
        return comment.commentid

    # 根据文章编号查询所有的一级评论(非回复评论),连带有用户表
    def find_by_articleid(self, articleid):
        results = db.session.query(Comment, Users).join(Users, Users.userid == Comment.userid).filter(
            Comment.articleid == articleid, Comment.hide == 0, Comment.replyid == 0).all()
        return results

    # 根据用户编号和日期进行查询是否已经超过每天5条限制
    def check_limit_per_10(self):
        start = time.strftime("%Y-%m-%d 00:00:00")  # 当天的起始时间
        end = time.strftime("%Y-%m-%d 23:59:59")  # 当天的结束时间
        result = db.session.query(Comment).filter(Comment.userid == session.get('userid'),
                                                  Comment.createtime.between(start, end)).all()
        if len(result) >= 10:
            return True  # 返回True表示今天已经不能再发表评论
        else:
            return False

    # 查询评论与用户信息，注意评论也需要分页
    def find_limit_with_user(self, articleid, start, count):
        result = db.session.query(Comment, Users).join(Users, Users.userid == Comment.userid) \
            .filter(Comment.articleid == articleid, Comment.hide == 0) \
            .order_by(Comment.commentid.desc()).limit(count).offset(start).all()
        return result

    # 插入回复
    def insert_reply(self, commentid, content, articleid, ipaddr):
        ctime = datetime.datetime.now()
        comment = Comment(userid=session.get('userid'), articleid=articleid, content=content, ipaddr=ipaddr,
                          createtime=ctime, updatetime=ctime, replyid=commentid)
        db.session.add(comment)
        db.session.commit()
        # print(comment.commentid+'这里')
        return comment.commentid

    # 获取某一文章的某一原始即一级评论的所有评论结果带有用户信息表
    def find_comment_with_user(self, commentid, articleid):
        results = db.session.query(Comment, Users).join(Users, Users.userid == Comment.userid).filter(
            Comment.articleid == articleid, Comment.replyid == commentid).all()
        return results

    # 递归获得所有一级评论的子孙回复评论,传入的参数是一级评论的id和文章id
    def find_descendants_replies_with_user(self, articleid, commentid):
        results = db.session.query(Comment, Users).join(Users, Users.userid == Comment.userid).filter(
            Comment.articleid == articleid, Comment.replyid == commentid).all()
        if len(results) == 0:
            return []
        comment_list = model_join_list(results)
        for comment in comment_list:
            # print(comment)
            comment['replies'] = self.find_descendants_replies_with_user(articleid, comment['commentid'])
            comment['opinion'] = Opinion().get_user_opinion(comment['commentid'])  # 增加评论的观点状态，-1是当前用户没有点赞和反对，1是赞成，0是反对。
        return comment_list

    # 获取文章底下的所有评论包含一级原始评论和底下的子孙评论，返回一个的时列表字典嵌套的结构de列表
    def get_all_comments_replies_with_user_list(self, articleid):
        primary_comments = model_join_list(self.find_by_articleid(articleid))
        lis = []
        dic = {}
        for primary_comment in primary_comments:
            primary_id = primary_comment['commentid']
            primary_comment['replies'] = self.find_descendants_replies_with_user(articleid, primary_id)
            primary_comment['opinion'] = Opinion().get_user_opinion(primary_id)  # 增加评论的观点状态，-1是当前用户没有点赞和反对，1是赞成，0是反对。
            dic.setdefault(primary_id, primary_comment)
        return dic

    # 更新点赞数量和反对数量
    def update_agree_oppose_count(self, commentid, agreecount, opposecount):
        comment_obj = db.session.query(Comment).filter_by(commentid=commentid).first()
        comment_obj.agreecount += agreecount
        comment_obj.opposecount += opposecount
        db.session.commit()

    # 获取某个评论的用户信息
    def get_user_info(self, commentid):
        row = db.session.query(Comment, Article.headline).join(Article, Article.articleid == Comment.articleid).filter(
            Comment.commentid == commentid, Comment.hide == 0).first()
        return row


# 点赞情况表
class Opinion(db.Model):
    __tablename__ = 'opinion'

    opinionid: Mapped[int] = mapped_column(Integer, primary_key=True)
    commentid: Mapped[Optional[int]] = mapped_column(Integer)
    userid: Mapped[Optional[int]] = mapped_column(Integer, server_default=text("'0'"))
    type: Mapped[Optional[int]] = mapped_column(TINYINT)
    ipaddr: Mapped[Optional[str]] = mapped_column(String(30))
    createtime: Mapped[Optional[datetime.datetime]] = mapped_column(DateTime)
    updatetime: Mapped[Optional[datetime.datetime]] = mapped_column(DateTime)

    # 获取某条评论当前作者点赞或反对的状态
    def get_user_opinion(self, commentid):
        row = db.session.query(Opinion.type).filter(Opinion.commentid == commentid,
                                                    Opinion.userid == session.get('userid')).first()
        # print(row)
        if row is None:
            return None
        else:
            return row[0]

    # 更改当前点赞或反对的状态
    def do_agree_oppose(self, commentid, like, ipaddr):  # like=0,点击了反对，like=1点击了赞成
        have_opinion = self.get_user_opinion(commentid)
        like = int(like)
        if have_opinion is None:  # 没有点过赞成反对
            ctime = datetime.datetime.now()
            # like=1,赞成type=1.like=0,赞成type=0
            opinion = Opinion(commentid=commentid, userid=session.get('userid'), type=like, ipaddr=ipaddr,
                              createtime=ctime, updatetime=ctime)
            db.session.add(opinion)
            db.session.commit()
            if like == 1:
                Comment().update_agree_oppose_count(commentid, 1, 0)
            else:
                Comment().update_agree_oppose_count(commentid, 0, 1)
            result = {"status": "do-new-opinion", "count": 1}
            return jsonify(result)  # 返回新的点赞或反对状态+1
        else:
            opinion = db.session.query(Opinion).filter(Opinion.commentid == commentid,
                                                       Opinion.userid == session.get('userid')).first()
            if have_opinion == 1:  # 已经点过赞
                if like == 1:  # 取消点赞
                    opinion.type = -1
                    db.session.commit()
                    Comment().update_agree_oppose_count(commentid, -1, 0)
                    result = {"status": "cancel-agree", "count": -1}
                    return jsonify(result)  # 返回取消状态，反对和点赞都不亮 赞成-1
                else:  # 反对，同时赞会取消
                    opinion.type = 0
                    db.session.commit()
                    Comment().update_agree_oppose_count(commentid, -1, 1)
                    result = {"status": "change-oppose", "count": 1}  # 反对+1，赞成-1
                    return jsonify(result)  # 反对键亮起，点赞键熄灭
            elif have_opinion == 0:  # 已经反对过
                if like == 1:  # 变成点赞
                    opinion.type = 1
                    db.session.commit()
                    Comment().update_agree_oppose_count(commentid, 1, -1)
                    result = {"status": "chang-agree", "count": 1}  # 点赞+1，反对-1
                    return jsonify(result)  # 返回取消状态，反对和点赞都不亮
                else:  # 反对，反对会取消
                    opinion.type = -1
                    db.session.commit()
                    Comment().update_agree_oppose_count(commentid, 0, -1)
                    result = {"status": "cancel-oppose", "count": -1}  # 反对-1
                    return jsonify(result)  # 反对键取消，点赞键取消
            elif have_opinion == -1:
                opinion.type = like
                db.session.commit()
                if like == 1:
                    Comment().update_agree_oppose_count(commentid, 1, 0)
                else:
                    Comment().update_agree_oppose_count(commentid, 0, 1)
                result = {"status": "redo-agree-oppose", "count": 1}  # 对应+1
                return jsonify(result)  # 重新点赞或反对
            else:
                result = {"status": "fail", "count": 0}  # 不变
                return jsonify(result)


class Notification(db.Model):
    __tablename__ = 'notification'

    id: Mapped[int] = mapped_column(Integer, primary_key=True)
    userid: Mapped[int] = mapped_column(Integer, comment='接收通知的用户ID')
    message: Mapped[str] = mapped_column(Text(collation='utf8mb4_general_ci'))
    is_read: Mapped[int] = mapped_column(TINYINT, server_default=text("'0'"))
    createdtime: Mapped[datetime.datetime] = mapped_column(DateTime)
    updatetime: Mapped[datetime.datetime] = mapped_column(DateTime)
    type: Mapped[Optional[str]] = mapped_column(String(255, 'utf8mb4_general_ci'),
                                                comment='reply\\mention\\system,like')
    source_user_id: Mapped[Optional[int]] = mapped_column(Integer)
    commentid: Mapped[Optional[int]] = mapped_column(Integer)
    articleid: Mapped[Optional[int]] = mapped_column(Integer)

    # 获取未读的各类通知数量
    def get_notifications_count(self):
        userid = session.get('userid')
        comment_count = db.session.query(Notification).filter_by(userid=userid, is_read=0, type='评论').count()
        like_count = db.session.query(Notification).filter_by(userid=userid, is_read=0, type='点赞').count()
        reply_count = db.session.query(Notification).filter_by(userid=userid, is_read=0, type='回复').count()
        system_count = db.session.query(Notification).filter_by(userid=userid, is_read=0, type='系统通知').count()
        notice_ls = {'comment_count': comment_count, 'like_count': like_count, 'reply_count': reply_count,
                     'system_count': system_count}
        return jsonify(notice_ls)

    # 根据类型获取通知的数量
    def get_notifications_count_by_type(self, is_read, userid, type):
        cnt = db.session.query(Notification).filter_by(userid=userid, is_read=is_read, type=type).count()
        return cnt

    # 向通知表里插入新的评论、回复、点赞等通知
    def insert_notification(self, type, commentid, articleid):
        ctime = datetime.datetime.now()
        info = Comment().get_user_info(commentid)  # 获得评论对象和文章标题
        comment_obj = info[0]
        # print(comment_obj.commentid)
        if type == '点赞':
            user_id = comment_obj.userid
            message = session.get('nickname') + '点赞了您在文章：' + info[1] + '中的评论'
            new_noti = Notification(userid=user_id, message=message, createdtime=ctime, updatetime=ctime, type=type,
                                    source_user_id=session.get('userid'), commentid=commentid, articleid=articleid)
            db.session.add(new_noti)
            db.session.commit()
        elif type == '评论':
            ar_info = Article().find_by_id(articleid=articleid)  # 返回article对象和作者昵称
            article_obj = ar_info[0]
            message = session.get('nickname') + '评论了您的文章《' + article_obj.headline + '》:' + comment_obj.content
            new_noti = Notification(userid=article_obj.userid, message=message, createdtime=ctime, updatetime=ctime,
                                    type=type,
                                    source_user_id=session.get('userid'), commentid=commentid, articleid=articleid)
            db.session.add(new_noti)
            db.session.commit()
        elif type == '回复':
            original_comment_id = comment_obj.replyid
            original_comment = Comment().get_user_info(original_comment_id)
            original_comment_obj = original_comment[0]
            message = session.get('nickname') + '回复了您在文章《' + info[1] + '》中的评论：' + comment_obj.content
            new_noti = Notification(userid=original_comment_obj.userid, message=message, createdtime=ctime,
                                    updatetime=ctime,
                                    type=type,
                                    source_user_id=session.get('userid'), commentid=commentid, articleid=articleid)
            db.session.add(new_noti)
            db.session.commit()
        else:
            pass

    # 清除所有通知内容
    def clear_all_notification(self):
        userid = session.get('userid')
        try:
            update_count = Notification.query.filter_by(userid=userid).update({'is_read': 1})
            db.session.commit()
        except Exception as e:
            print('error:', e)


class Memos(db.Model):
    __tablename__ = 'memos'

    id: Mapped[int] = mapped_column(Integer, primary_key=True)
    title: Mapped[str] = mapped_column(String(255, 'utf8mb4_general_ci'), server_default=text("'今日计划'"))
    userid: Mapped[int] = mapped_column(Integer)
    content: Mapped[Optional[str]] = mapped_column(LONGTEXT)
    is_pinned: Mapped[Optional[int]] = mapped_column(TINYINT, server_default=text("'0'"))
    is_archived: Mapped[Optional[int]] = mapped_column(TINYINT, server_default=text("'0'"))
    createdtime: Mapped[Optional[datetime.datetime]] = mapped_column(DateTime)
    updatedtime: Mapped[Optional[datetime.datetime]] = mapped_column(DateTime)
    hide: Mapped[Optional[int]] = mapped_column(TINYINT, server_default=text("'0'"))

    def find_memos_by_user(self):
        userid = session.get('userid')
        results = db.session.query(Memos).filter_by(userid=userid, hide=0).order_by(Memos.updatedtime.desc()).all()
        return results

    def find_memo_by_id(self, memoid):
        row = db.session.query(Memos).filter_by(id=memoid, hide=0).first()
        return row

    def delete_memo_by_id(self, memoid):
        row = db.session.query(Memos).filter_by(id=memoid, hide=0).first()
        row.hide = 1
        db.session.commit()

    def update_memo(self, memoid, title, content, updatedtime):
        row = db.session.query(Memos).filter_by(id=memoid, hide=0).first()
        row.title = title
        row.content = content
        row.updatedtime = updatedtime
        db.session.commit()
        return row

    def insert_memo(self, title, content):
        userid = session.get('userid')
        ctime = datetime.datetime.now().isoformat()
        memo = Memos(title=title, content=content, userid=userid, createdtime=ctime, updatedtime=ctime)
        db.session.add(memo)
        db.session.commit()
        return memo

    # 序列化单行模型的方法，得到的是一行的一个字典
    def to_dict(self):
        return {column.name: getattr(self, column.name) for column in self.__table__.columns}
